package com.chinasoft.bigdatadisplay.mapper;


import com.chinasoft.bigdatadisplay.entity.*;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ResultDisplayMapper {




    // 按地区查询平均消费
    @Select("select location,avg_consumption from location_consumption_stats where location = #{location} ")
    AvgConsumptionOfLocation getAvgConsumptionOfLocation(
            @Param("location") String location);



    // 按年龄查询平均消费
    @Select("select age,avg_consumption from age_consumption_stats where age = #{age} ")
    AvgConsumptionOfAge getAvgConsumptionOfAge(
            @Param("age") String age);


    // 按年龄查询每个分类的销量，取销量最多的前20条
    @Select("select age, category, sum " +
            "from category_age_stats " +
            "where age = #{age} " +
            "order by sum desc " +  // 按销量降序排序（从高到低）
            "limit 20")             // 只取前20条
    List<SumCategoryOfAge> getSumCategoryOfAge(@Param("age") String age);


    @Select("select brand, sales_count, total_sales " +
            "from brand_sales_stats " +
            "order by sales_count desc " +
            "limit 20")
    List<BrandSalesStats> getBrandSalesStats();

    @Select("select category, sales_count, total_sales " +
            "from category_sales_stats " +
            "order by sales_count desc " +
            "limit 20")
    List<CategorySalesStats> getCategorySalesStats();


    @Select("select sex ,total_sales, proportion "+
            "from gender_sales_summary ")
    List<GenderSalesSummary> getGenderSalesSummary();

    /**
     * 查询所有月度销售统计数据
     */
    @Select("SELECT id, time, sales_count AS salesCount, total_sales AS totalSales " +
            "FROM monthly_sales_stats " +
            "ORDER BY time ASC")

    List<MonthlySalesStats> listAll();


    @Select("select * from user_purchase_stats")
    List<UserPurchaseStats> getUserPurchaseStats();
}
